package dao;

import util.DatabaseConnection;

import java.sql.*;
import java.time.LocalDate;
import java.util.*;

/**
 * 管理员统计数据访问对象
 */
public class AdminStatisticsDAO {

    /**
     * 获取系统总体统计信息
     */
    public Map<String, Object> getSystemOverviewStats() throws SQLException {
        Map<String, Object> stats = new HashMap<>();

        try (Connection conn = DatabaseConnection.getConnection()) {
            // 总预约数
            String totalReservationsSql = "SELECT COUNT(*) as total FROM reservations";
            try (PreparedStatement stmt = conn.prepareStatement(totalReservationsSql);
                    ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    stats.put("totalReservations", rs.getInt("total"));
                }
            }

            // 今日预约数
            String todayReservationsSql = "SELECT COUNT(*) as total FROM reservations WHERE reservation_date = CURDATE()";
            try (PreparedStatement stmt = conn.prepareStatement(todayReservationsSql);
                    ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    stats.put("todayReservations", rs.getInt("total"));
                }
            }

            // 座位利用率
            String seatUtilizationSql = """
                        SELECT
                            ROUND((SELECT COUNT(*) FROM reservations WHERE reservation_date = CURDATE()) * 100.0 /
                            (SELECT COUNT(*) FROM seats WHERE base_status != 2), 2) as utilization_rate
                    """;
            try (PreparedStatement stmt = conn.prepareStatement(seatUtilizationSql);
                    ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    stats.put("seatUtilizationRate", rs.getDouble("utilization_rate"));
                }
            }

            // 热门时段
            String popularTimeSql = """
                        SELECT
                            CASE
                                WHEN start_time = '08:00:00' THEN '上午'
                                WHEN start_time = '13:00:00' THEN '下午'
                                WHEN start_time = '18:00:00' THEN '晚上'
                                ELSE '其他'
                            END as time_slot,
                            COUNT(*) as count
                        FROM reservations
                        WHERE reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                        GROUP BY start_time
                        ORDER BY count DESC
                        LIMIT 1
                    """;
            try (PreparedStatement stmt = conn.prepareStatement(popularTimeSql);
                    ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    stats.put("popularTimeSlot", rs.getString("time_slot"));
                } else {
                    stats.put("popularTimeSlot", "上午");
                }
            }
        }

        return stats;
    }

    /**
     * 获取系统预约趋势数据
     */
    public Map<String, Object> getSystemTrendData(String period) throws SQLException {
        Map<String, Object> result = new HashMap<>();

        String sql;
        String[] labels;

        switch (period) {
            case "今日":
                sql = """
                            SELECT HOUR(create_time) as hour, COUNT(*) as count
                            FROM reservations
                            WHERE reservation_date = CURDATE()
                            GROUP BY HOUR(create_time)
                            ORDER BY hour
                        """;
                labels = new String[] { "00", "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11",
                        "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23" };
                break;

            case "本周":
                sql = """
                            SELECT DAYOFWEEK(reservation_date) as day, COUNT(*) as count
                            FROM reservations
                            WHERE reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                                AND reservation_date <= CURDATE()
                            GROUP BY DAYOFWEEK(reservation_date)
                            ORDER BY day
                        """;
                labels = new String[] { "周日", "周一", "周二", "周三", "周四", "周五", "周六" };
                break;

            case "本月":
                sql = """
                            SELECT WEEK(reservation_date) - WEEK(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY)) + 1 as week,
                                   COUNT(*) as count
                            FROM reservations
                            WHERE reservation_date >= DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY)
                                AND reservation_date <= CURDATE()
                            GROUP BY WEEK(reservation_date)
                            ORDER BY week
                        """;
                labels = new String[] { "第1周", "第2周", "第3周", "第4周", "第5周" };
                break;

            default:
                sql = """
                            SELECT DAYOFWEEK(reservation_date) as day, COUNT(*) as count
                            FROM reservations
                            WHERE reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                                AND reservation_date <= CURDATE()
                            GROUP BY DAYOFWEEK(reservation_date)
                            ORDER BY day
                        """;
                labels = new String[] { "周日", "周一", "周二", "周三", "周四", "周五", "周六" };
                break;
        }

        int[] dataPoints = new int[labels.length];

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                int index = rs.getInt(1) - 1; // 数据库返回的是1-based
                if (period.equals("今日")) {
                    index = rs.getInt("hour");
                } else if (period.equals("本周")) {
                    index = rs.getInt("day") - 1; // DAYOFWEEK返回1-7，需要转换为0-6
                } else if (period.equals("本月")) {
                    index = rs.getInt("week") - 1;
                }

                if (index >= 0 && index < dataPoints.length) {
                    dataPoints[index] = rs.getInt("count");
                }
            }
        }

        result.put("labels", labels);
        result.put("data", dataPoints);
        return result;
    }

    /**
     * 获取系统时间段分布数据
     */
    public Map<String, Object> getSystemTimeSlotData(String period) throws SQLException {
        Map<String, Object> result = new HashMap<>();

        String sql;
        switch (period) {
            case "今日":
                sql = """
                            SELECT
                                CASE
                                    WHEN start_time = '08:00:00' THEN '上午'
                                    WHEN start_time = '13:00:00' THEN '下午'
                                    WHEN start_time = '18:00:00' THEN '晚上'
                                    ELSE '其他'
                                END as time_slot,
                                COUNT(*) as count
                            FROM reservations
                            WHERE reservation_date = CURDATE()
                            GROUP BY start_time
                            ORDER BY start_time
                        """;
                break;

            case "本周":
                sql = """
                            SELECT
                                CASE
                                    WHEN start_time = '08:00:00' THEN '上午'
                                    WHEN start_time = '13:00:00' THEN '下午'
                                    WHEN start_time = '18:00:00' THEN '晚上'
                                    ELSE '其他'
                                END as time_slot,
                                COUNT(*) as count
                            FROM reservations
                            WHERE reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                                AND reservation_date <= CURDATE()
                            GROUP BY start_time
                            ORDER BY start_time
                        """;
                break;

            case "本月":
                sql = """
                            SELECT
                                CASE
                                    WHEN start_time = '08:00:00' THEN '上午'
                                    WHEN start_time = '13:00:00' THEN '下午'
                                    WHEN start_time = '18:00:00' THEN '晚上'
                                    ELSE '其他'
                                END as time_slot,
                                COUNT(*) as count
                            FROM reservations
                            WHERE reservation_date >= DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY)
                                AND reservation_date <= CURDATE()
                            GROUP BY start_time
                            ORDER BY start_time
                        """;
                break;

            default:
                sql = """
                            SELECT
                                CASE
                                    WHEN start_time = '08:00:00' THEN '上午'
                                    WHEN start_time = '13:00:00' THEN '下午'
                                    WHEN start_time = '18:00:00' THEN '晚上'
                                    ELSE '其他'
                                END as time_slot,
                                COUNT(*) as count
                            FROM reservations
                            WHERE reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                                AND reservation_date <= CURDATE()
                            GROUP BY start_time
                            ORDER BY start_time
                        """;
                break;
        }

        Map<String, Integer> timeSlotCounts = new HashMap<>();
        timeSlotCounts.put("上午", 0);
        timeSlotCounts.put("下午", 0);
        timeSlotCounts.put("晚上", 0);

        int total = 0;

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                String timeSlot = rs.getString("time_slot");
                int count = rs.getInt("count");
                timeSlotCounts.put(timeSlot, count);
                total += count;
            }
        }

        // 计算百分比
        int[] percentages = new int[3];
        if (total > 0) {
            percentages[0] = (int) Math.round(timeSlotCounts.get("上午") * 100.0 / total);
            percentages[1] = (int) Math.round(timeSlotCounts.get("下午") * 100.0 / total);
            percentages[2] = (int) Math.round(timeSlotCounts.get("晚上") * 100.0 / total);
        }

        result.put("labels", new String[] { "上午", "下午", "晚上" });
        result.put("data", percentages);
        result.put("counts",
                new int[] { timeSlotCounts.get("上午"), timeSlotCounts.get("下午"), timeSlotCounts.get("晚上") });
        return result;
    }

    /**
     * 获取区域使用分布数据
     */
    public Map<String, Object> getAreaDistributionData(String period) throws SQLException {
        Map<String, Object> result = new HashMap<>();

        String sql;
        switch (period) {
            case "今日":
                sql = """
                            SELECT s.area, COUNT(*) as count
                            FROM reservations r
                            JOIN seats s ON r.seat_id = s.seat_id
                            WHERE r.reservation_date = CURDATE()
                            GROUP BY s.area
                            ORDER BY count DESC
                        """;
                break;

            case "本周":
                sql = """
                            SELECT s.area, COUNT(*) as count
                            FROM reservations r
                            JOIN seats s ON r.seat_id = s.seat_id
                            WHERE r.reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                                AND r.reservation_date <= CURDATE()
                            GROUP BY s.area
                            ORDER BY count DESC
                        """;
                break;

            case "本月":
                sql = """
                            SELECT s.area, COUNT(*) as count
                            FROM reservations r
                            JOIN seats s ON r.seat_id = s.seat_id
                            WHERE r.reservation_date >= DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY)
                                AND r.reservation_date <= CURDATE()
                            GROUP BY s.area
                            ORDER BY count DESC
                        """;
                break;

            default:
                sql = """
                            SELECT s.area, COUNT(*) as count
                            FROM reservations r
                            JOIN seats s ON r.seat_id = s.seat_id
                            WHERE r.reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                                AND r.reservation_date <= CURDATE()
                            GROUP BY s.area
                            ORDER BY count DESC
                        """;
                break;
        }

        List<String> areas = new ArrayList<>();
        List<Integer> counts = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                areas.add(rs.getString("area"));
                counts.add(rs.getInt("count"));
            }
        }

        result.put("labels", areas.toArray(new String[0]));
        result.put("data", counts.stream().mapToInt(Integer::intValue).toArray());
        return result;
    }

    /**
     * 获取热力图数据（每日每小时的预约情况）
     */
    public Map<String, Object> getHeatmapData(String period) throws SQLException {
        Map<String, Object> result = new HashMap<>();

        String sql;
        switch (period) {
            case "今日":
                sql = """
                            SELECT
                                CASE
                                    WHEN start_time = '08:00:00' THEN 0
                                    WHEN start_time = '13:00:00' THEN 1
                                    WHEN start_time = '18:00:00' THEN 2
                                    ELSE 3
                                END as time_index,
                                COUNT(*) as count
                            FROM reservations
                            WHERE reservation_date = CURDATE()
                            GROUP BY start_time
                        """;
                break;

            case "本周":
                sql = """
                            SELECT
                                DAYOFWEEK(reservation_date) - 1 as day_index,
                                CASE
                                    WHEN start_time = '08:00:00' THEN 0
                                    WHEN start_time = '13:00:00' THEN 1
                                    WHEN start_time = '18:00:00' THEN 2
                                    ELSE 3
                                END as time_index,
                                COUNT(*) as count
                            FROM reservations
                            WHERE reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                                AND reservation_date <= CURDATE()
                            GROUP BY DAYOFWEEK(reservation_date), start_time
                        """;
                break;

            default:
                sql = """
                            SELECT
                                DAYOFWEEK(reservation_date) - 1 as day_index,
                                CASE
                                    WHEN start_time = '08:00:00' THEN 0
                                    WHEN start_time = '13:00:00' THEN 1
                                    WHEN start_time = '18:00:00' THEN 2
                                    ELSE 3
                                END as time_index,
                                COUNT(*) as count
                            FROM reservations
                            WHERE reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                                AND reservation_date <= CURDATE()
                            GROUP BY DAYOFWEEK(reservation_date), start_time
                        """;
                break;
        }

        // 初始化热力图数据矩阵
        int[][] heatmapData;
        if (period.equals("今日")) {
            heatmapData = new int[1][3]; // 1天，3个时段
        } else {
            heatmapData = new int[7][3]; // 7天，3个时段
        }

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                int timeIndex = rs.getInt("time_index");
                int count = rs.getInt("count");

                if (period.equals("今日")) {
                    if (timeIndex < 3) {
                        heatmapData[0][timeIndex] = count;
                    }
                } else {
                    int dayIndex = rs.getInt("day_index");
                    if (dayIndex >= 0 && dayIndex < 7 && timeIndex < 3) {
                        heatmapData[dayIndex][timeIndex] = count;
                    }
                }
            }
        }

        result.put("data", heatmapData);
        result.put("period", period);
        return result;
    }
}
